----------------------------[ change NLS_DATE_FORMAT ]---------------------------------- select sysdate from dual; alter session set NLS_DATE_FORMAT='yy/mm/dd'; ----------------------------[ create table exceptions ]---------------------------------- col owner for a10 col table_name for a10 col constraint for a22 drop table exceptions cascade constraints purge ; create table exceptions (err_rowid rowid ,owner varchar2(128) ,table_name varchar2(128) ,constraint varchar2(128)); ----------------------------[ check constraints ]---------------------------------- select owner,TABLE_NAME, Lpad(CONSTRAINT_NAME,27,' '), CONSTRAINT_TYPE, STATUS from user_constraints where CONSTRAINT_NAME like '%FK' order by 2; /* S1E COUNTRIES COUNTRIES_REGIONS_FK R ENABLED S1E DEPARTMENTS DEPARTMENTS_EMPLOYEES_FK R ENABLED S1E DEPARTMENTS DEPARTMENTS_LOCATIONS_FK R ENABLED S1E EMPLOYEES EMPLOYEES_DEPARTMENTS_FK R ENABLED S1E EMPLOYEES EMPLOYEES_EMPLOYEES_FK R ENABLED S1E EMPLOYEES EMPLOYEES_JOBS_FK R ENABLED S1E JOB_HISTORY JOB_HISTORY_JOBS_FK R ENABLED S1E JOB_HISTORY JOB_HISTORY_EMPLOYEES_FK R ENABLED S1E JOB_HISTORY JOB_HISTORY_DEPARTMENTS_FK R ENABLED S1E LOCATIONS LOCATIONS_COUNTRIES_FK R ENABLED */ ----------------------------[ disable constraints ]---------------------------------- /* select Q'[alter table ]'||Rpad(TABLE_NAME,13,' ') ||Q'[ disable constraint ]'||Lpad(CONSTRAINT_NAME,27,' ')||Q'[;]' from user_constraints where CONSTRAINT_NAME like '%FK'; */ alter table LOCATIONS disable constraint LOCATIONS_COUNTRIES_FK; alter table EMPLOYEES disable constraint EMPLOYEES_DEPARTMENTS_FK; alter table JOB_HISTORY disable constraint JOB_HISTORY_DEPARTMENTS_FK; alter table DEPARTMENTS disable constraint DEPARTMENTS_EMPLOYEES_FK; alter table EMPLOYEES disable constraint EMPLOYEES_EMPLOYEES_FK; alter table JOB_HISTORY disable constraint JOB_HISTORY_EMPLOYEES_FK; alter table EMPLOYEES disable constraint EMPLOYEES_JOBS_FK; alter table JOB_HISTORY disable constraint JOB_HISTORY_JOBS_FK; alter table DEPARTMENTS disable constraint DEPARTMENTS_LOCATIONS_FK; alter table COUNTRIES disable constraint COUNTRIES_REGIONS_FK; ------------[ truncate table ]------------------------------ /* select Q'[truncate table ]'||TNAME ||Q'[ ;]' from tab; */ truncate table COUNTRIES; truncate table JOBS; truncate table LOCATIONS; truncate table REGIONS; truncate table DEPARTMENTS; truncate table EMPLOYEES; truncate table JOB_HISTORY; ------------------ REGIONS; 4 row--------------------------------------- /* select Q'[insert into REGIONS values(]' ||Lpad(REGION_ID,4,' ')||',' ||Rpad(''''||REGION_NAME||'''',25,' ') ||Q'[);]' from REGIONS; */ insert into REGIONS values( 1,'Europe' ); insert into REGIONS values( 2,'Americas' ); insert into REGIONS values( 3,'Asia' ); insert into REGIONS values( 4,'Middle East and Africa' ); commit; --------------------- COUNTRIES; 25 row ---------------------- /* select Q'[insert into COUNTRIES values(]' ||Rpad(Q'[']' ||COUNTRY_ID ||Q'[']',5,' ') ||Rpad(Q'[,']'||COUNTRY_NAME||'''',27,' ') ||Q'[,]' ||Lpad(REGION_ID,4,' ') ||Q'[);]' from COUNTRIES; */ insert into COUNTRIES values('AR' ,'Argentina' , 2); insert into COUNTRIES values('AU' ,'Australia' , 3); insert into COUNTRIES values('BE' ,'Belgium' , 1); insert into COUNTRIES values('BR' ,'Brazil' , 2); insert into COUNTRIES values('CA' ,'Canada' , 2); insert into COUNTRIES values('CH' ,'Switzerland' , 1); insert into COUNTRIES values('CN' ,'China' , 3); insert into COUNTRIES values('DE' ,'Germany' , 1); insert into COUNTRIES values('DK' ,'Denmark' , 1); insert into COUNTRIES values('EG' ,'Egypt' , 4); insert into COUNTRIES values('FR' ,'France' , 1); insert into COUNTRIES values('IL' ,'Israel' , 4); insert into COUNTRIES values('IN' ,'India' , 3); insert into COUNTRIES values('IT' ,'Italy' , 1); insert into COUNTRIES values('JP' ,'Japan' , 3); insert into COUNTRIES values('KW' ,'Kuwait' , 4); insert into COUNTRIES values('ML' ,'Malaysia' , 3); insert into COUNTRIES values('MX' ,'Mexico' , 2); insert into COUNTRIES values('NG' ,'Nigeria' , 4); insert into COUNTRIES values('NL' ,'Netherlands' , 1); insert into COUNTRIES values('SG' ,'Singapore' , 3); insert into COUNTRIES values('UK' ,'United Kingdom' , 1); insert into COUNTRIES values('US' ,'United States of America', 2); insert into COUNTRIES values('ZM' ,'Zambia' , 4); insert into COUNTRIES values('ZW' ,'Zimbabwe' , 4); commit; ------------------------- LOCATIONS 23 row -------------------------- /* select Q'[insert into LOCATIONS values(]' ||Lpad(LOCATION_ID ,5,' ') ||Q'[,]' ||Rpad(Q'[']'||STREET_ADDRESS||'''',44,' ') ||Q'[,]' ||Rpad(Q'[']'||POSTAL_CODE||Q'[']',14,' ') ||Q'[,]' --문자열 null 처리 ||Rpad(Q'[']'||CITY||Q'[']',21,' ') ||Q'[,]' ||Rpad(Q'[']'||STATE_PROVINCE||Q'[']',22,' ') ||Q'[,]' ||Rpad(Q'[']'||COUNTRY_ID||Q'[']',4,' ') ||Q'[);]' from LOCATIONS; */ insert into LOCATIONS values( 1000,'1297 Via Cola di Rie' ,'00989' ,'Roma' ,'' ,'IT'); insert into LOCATIONS values( 1100,'93091 Calle della Testa' ,'10934' ,'Venice' ,'' ,'IT'); insert into LOCATIONS values( 1200,'2017 Shinjuku-ku' ,'1689' ,'Tokyo' ,'Tokyo Prefecture' ,'JP'); insert into LOCATIONS values( 1300,'9450 Kamiya-cho' ,'6823' ,'Hiroshima' ,'' ,'JP'); insert into LOCATIONS values( 1400,'2014 Jabberwocky Rd' ,'26192' ,'Southlake' ,'Texas' ,'US'); insert into LOCATIONS values( 1500,'2011 Interiors Blvd' ,'99236' ,'South San Francisco','California' ,'US'); insert into LOCATIONS values( 1600,'2007 Zagora St' ,'50090' ,'South Brunswick' ,'New Jersey' ,'US'); insert into LOCATIONS values( 1700,'2004 Charade Rd' ,'98199' ,'Seattle' ,'Washington' ,'US'); insert into LOCATIONS values( 1800,'147 Spadina Ave' ,'M5V 2L7' ,'Toronto' ,'Ontario' ,'CA'); insert into LOCATIONS values( 1900,'6092 Boxwood St' ,'YSW 9T2' ,'Whitehorse' ,'Yukon' ,'CA'); insert into LOCATIONS values( 2000,'40-5-12 Laogianggen' ,'190518' ,'Beijing' ,'' ,'CN'); insert into LOCATIONS values( 2100,'1298 Vileparle (E)' ,'490231' ,'Bombay' ,'Maharashtra' ,'IN'); insert into LOCATIONS values( 2200,'12-98 Victoria Street' ,'2901' ,'Sydney' ,'New South Wales' ,'AU'); insert into LOCATIONS values( 2300,'198 Clementi North' ,'540198' ,'Singapore' ,'' ,'SG'); insert into LOCATIONS values( 2400,'8204 Arthur St' ,'' ,'London' ,'' ,'UK'); insert into LOCATIONS values( 2500,'Magdalen Centre, The Oxford Science Park' ,'OX9 9ZB' ,'Oxford' ,'Oxford' ,'UK'); insert into LOCATIONS values( 2600,'9702 Chester Road' ,'09629850293' ,'Stretford' ,'Manchester' ,'UK'); insert into LOCATIONS values( 2700,'Schwanthalerstr. 7031' ,'80925' ,'Munich' ,'Bavaria' ,'DE'); insert into LOCATIONS values( 2800,'Rua Frei Caneca 1360 ' ,'01307-002' ,'Sao Paulo' ,'Sao Paulo' ,'BR'); insert into LOCATIONS values( 2900,'20 Rue des Corps-Saints' ,'1730' ,'Geneva' ,'Geneve' ,'CH'); insert into LOCATIONS values( 3000,'Murtenstrasse 921' ,'3095' ,'Bern' ,'BE' ,'CH'); insert into LOCATIONS values( 3100,'Pieter Breughelstraat 837' ,'3029SK' ,'Utrecht' ,'Utrecht' ,'NL'); insert into LOCATIONS values( 3200,'Mariano Escobedo 9991' ,'11932' ,'Mexico City' ,'Distrito Federal,' ,'MX'); commit; ------------------------ JOBS 19 row--------------------------------- /* select Q'[insert into JOBS values(]' ||Rpad(Q'[']'||JOB_ID||Q'[']' ,12,' ') ||Q'[,]' ||Rpad(Q'[']'||JOB_TITLE||'''',34,' ') ||Q'[,]' ||Lpad(MAX_SALARY,7,' ') ||Q'[,]' ||Lpad(MIN_SALARY,7,' ') ||Q'[);]' from JOBS; */ insert into JOBS values('AD_PRES' ,'President' , 40000, 20080); insert into JOBS values('AD_VP' ,'Administration Vice President' , 30000, 15000); insert into JOBS values('AD_ASST' ,'Administration Assistant' , 6000, 3000); insert into JOBS values('FI_MGR' ,'Finance Manager' , 16000, 8200); insert into JOBS values('FI_ACCOUNT','Accountant' , 9000, 4200); insert into JOBS values('AC_MGR' ,'Accounting Manager' , 16000, 8200); insert into JOBS values('AC_ACCOUNT','Public Accountant' , 9000, 4200); insert into JOBS values('SA_MAN' ,'Sales Manager' , 20080, 10000); insert into JOBS values('SA_REP' ,'Sales Representative' , 12008, 6000); insert into JOBS values('PU_MAN' ,'Purchasing Manager' , 15000, 8000); insert into JOBS values('PU_CLERK' ,'Purchasing Clerk' , 5500, 2500); insert into JOBS values('ST_MAN' ,'Stock Manager' , 8500, 5500); insert into JOBS values('ST_CLERK' ,'Stock Clerk' , 5000, 2008); insert into JOBS values('SH_CLERK' ,'Shipping Clerk' , 5500, 2500); insert into JOBS values('IT_PROG' ,'Programmer' , 10000, 4000); insert into JOBS values('MK_MAN' ,'Marketing Manager' , 15000, 9000); insert into JOBS values('MK_REP' ,'Marketing Representative' , 9000, 4000); insert into JOBS values('HR_REP' ,'Human Resources Representative' , 9000, 4000); insert into JOBS values('PR_REP' ,'Public Relations Representative' , 10500, 4500); commit; ----------------------- DEPARTMENTS; 27 row---------------------- /* select Q'[insert into DEPARTMENTS values(]' ||Lpad(DEPARTMENT_ID,4,' ')||',' ||Rpad(''''||DEPARTMENT_NAME||'''',25,' ')||',' ||Lpad(NVL2(MANAGER_ID ,to_char(MANAGER_ID),'null'),5,' ')||',' --null ||Lpad(LOCATION_ID,6,' ')||Q'[);]' from DEPARTMENTS; */ insert into DEPARTMENTS values( 10,'Administration' , 200, 1700); insert into DEPARTMENTS values( 20,'Marketing' , 201, 1800); insert into DEPARTMENTS values( 30,'Purchasing' , 114, 1700); insert into DEPARTMENTS values( 40,'Human Resources' , 203, 2400); insert into DEPARTMENTS values( 50,'Shipping' , 121, 1500); insert into DEPARTMENTS values( 60,'IT' , 103, 1400); insert into DEPARTMENTS values( 70,'Public Relations' , 204, 2700); insert into DEPARTMENTS values( 80,'Sales' , 145, 2500); insert into DEPARTMENTS values( 90,'Executive' , 100, 1700); insert into DEPARTMENTS values( 100,'Finance' , 108, 1700); insert into DEPARTMENTS values( 110,'Accounting' , 205, 1700); insert into DEPARTMENTS values( 120,'Treasury' , null, 1700); insert into DEPARTMENTS values( 130,'Corporate Tax' , null, 1700); insert into DEPARTMENTS values( 140,'Control And Credit' , null, 1700); insert into DEPARTMENTS values( 150,'Shareholder Services' , null, 1700); insert into DEPARTMENTS values( 160,'Benefits' , null, 1700); insert into DEPARTMENTS values( 170,'Manufacturing' , null, 1700); insert into DEPARTMENTS values( 180,'Construction' , null, 1700); insert into DEPARTMENTS values( 190,'Contracting' , null, 1700); insert into DEPARTMENTS values( 200,'Operations' , null, 1700); insert into DEPARTMENTS values( 210,'IT Support' , null, 1700); insert into DEPARTMENTS values( 220,'NOC' , null, 1700); insert into DEPARTMENTS values( 230,'IT Helpdesk' , null, 1700); insert into DEPARTMENTS values( 240,'Government Sales' , null, 1700); insert into DEPARTMENTS values( 250,'Retail Sales' , null, 1700); insert into DEPARTMENTS values( 260,'Recruiting' , null, 1700); insert into DEPARTMENTS values( 270,'Payroll' , null, 1700); commit; -----------------------EMPLOYEES; 107 row----------------------------- /* select Q'[insert into EMPLOYEES values(]' ||Rpad(Q'[,']'||FIRST_NAME||Q'[']',14,' ') ||Rpad(Q'[,']'||LAST_NAME ||Q'[']',14,' ') ||Rpad(Q'[,']'||EMAIL ||Q'[']',11,' ') ||Rpad(Q'[,']'||PHONE_NUMBER||Q'[']',21,' ') ||Rpad(Q'[,']'||HIRE_DATE||Q'[']',11,' ') ||Q'[,']' ||Rpad(JOB_ID||Q'[']',12,' ') ||Q'[,]' ||Lpad(SALARY,6,' ') ||Q'[,]' ||Lpad(nvl(to_char(COMMISSION_PCT),'null'),4,' ') ||Q'[,]' --null ||Lpad(nvl(to_char(MANAGER_ID),'NULL') ,4,' ') ||Q'[,]' ||Lpad(nvl(to_char(DEPARTMENT_ID),'null'),4,' ') ||Q'[);]' as aaa from EMPLOYEES; */ insert into EMPLOYEES values(100,'Steven' ,'King' ,'SKING' ,'515.123.4567' ,'03/06/17','AD_PRES' , 24000,null,NULL, 90); insert into EMPLOYEES values(101,'Neena' ,'Kochhar' ,'NKOCHHAR','515.123.4568' ,'05/09/21','AD_VP' , 17000,null, 100, 90); insert into EMPLOYEES values(102,'Lex' ,'De Haan' ,'LDEHAAN' ,'515.123.4569' ,'01/01/13','AD_VP' , 17000,null, 100, 90); insert into EMPLOYEES values(103,'Alexander' ,'Hunold' ,'AHUNOLD' ,'590.423.4567' ,'06/01/03','IT_PROG' , 9000,null, 102, 60); insert into EMPLOYEES values(104,'Bruce' ,'Ernst' ,'BERNST' ,'590.423.4568' ,'07/05/21','IT_PROG' , 6000,null, 103, 60); insert into EMPLOYEES values(105,'David' ,'Austin' ,'DAUSTIN' ,'590.423.4569' ,'05/06/25','IT_PROG' , 4800,null, 103, 60); insert into EMPLOYEES values(106,'Valli' ,'Pataballa' ,'VPATABAL','590.423.4560' ,'06/02/05','IT_PROG' , 4800,null, 103, 60); insert into EMPLOYEES values(107,'Diana' ,'Lorentz' ,'DLORENTZ','590.423.5567' ,'07/02/07','IT_PROG' , 4200,null, 103, 60); insert into EMPLOYEES values(108,'Nancy' ,'Greenberg' ,'NGREENBE','515.124.4569' ,'02/08/17','FI_MGR' , 12008,null, 101, 100); insert into EMPLOYEES values(109,'Daniel' ,'Faviet' ,'DFAVIET' ,'515.124.4169' ,'02/08/16','FI_ACCOUNT' , 9000,null, 108, 100); insert into EMPLOYEES values(110,'John' ,'Chen' ,'JCHEN' ,'515.124.4269' ,'05/09/28','FI_ACCOUNT' , 8200,null, 108, 100); insert into EMPLOYEES values(111,'Ismael' ,'Sciarra' ,'ISCIARRA','515.124.4369' ,'05/09/30','FI_ACCOUNT' , 7700,null, 108, 100); insert into EMPLOYEES values(112,'Jose Manuel','Urman' ,'JMURMAN' ,'515.124.4469' ,'06/03/07','FI_ACCOUNT' , 7800,null, 108, 100); insert into EMPLOYEES values(113,'Luis' ,'Popp' ,'LPOPP' ,'515.124.4567' ,'07/12/07','FI_ACCOUNT' , 6900,null, 108, 100); insert into EMPLOYEES values(114,'Den' ,'Raphaely' ,'DRAPHEAL','515.127.4561' ,'02/12/07','PU_MAN' , 11000,null, 100, 30); insert into EMPLOYEES values(115,'Alexander' ,'Khoo' ,'AKHOO' ,'515.127.4562' ,'03/05/18','PU_CLERK' , 3100,null, 114, 30); insert into EMPLOYEES values(116,'Shelli' ,'Baida' ,'SBAIDA' ,'515.127.4563' ,'05/12/24','PU_CLERK' , 2900,null, 114, 30); insert into EMPLOYEES values(117,'Sigal' ,'Tobias' ,'STOBIAS' ,'515.127.4564' ,'05/07/24','PU_CLERK' , 2800,null, 114, 30); insert into EMPLOYEES values(118,'Guy' ,'Himuro' ,'GHIMURO' ,'515.127.4565' ,'06/11/15','PU_CLERK' , 2600,null, 114, 30); insert into EMPLOYEES values(119,'Karen' ,'Colmenares' ,'KCOLMENA','515.127.4566' ,'07/08/10','PU_CLERK' , 2500,null, 114, 30); insert into EMPLOYEES values(120,'Matthew' ,'Weiss' ,'MWEISS' ,'650.123.1234' ,'04/07/18','ST_MAN' , 8000,null, 100, 50); insert into EMPLOYEES values(121,'Adam' ,'Fripp' ,'AFRIPP' ,'650.123.2234' ,'05/04/10','ST_MAN' , 8200,null, 100, 50); insert into EMPLOYEES values(122,'Payam' ,'Kaufling' ,'PKAUFLIN','650.123.3234' ,'03/05/01','ST_MAN' , 7900,null, 100, 50); insert into EMPLOYEES values(123,'Shanta' ,'Vollman' ,'SVOLLMAN','650.123.4234' ,'05/10/10','ST_MAN' , 6500,null, 100, 50); insert into EMPLOYEES values(124,'Kevin' ,'Mourgos' ,'KMOURGOS','650.123.5234' ,'07/11/16','ST_MAN' , 5800,null, 100, 50); insert into EMPLOYEES values(125,'Julia' ,'Nayer' ,'JNAYER' ,'650.124.1214' ,'05/07/16','ST_CLERK' , 3200,null, 120, 50); insert into EMPLOYEES values(126,'Irene' ,'Mikkilineni','IMIKKILI','650.124.1224' ,'06/09/28','ST_CLERK' , 2700,null, 120, 50); insert into EMPLOYEES values(127,'James' ,'Landry' ,'JLANDRY' ,'650.124.1334' ,'07/01/14','ST_CLERK' , 2400,null, 120, 50); insert into EMPLOYEES values(128,'Steven' ,'Markle' ,'SMARKLE' ,'650.124.1434' ,'08/03/08','ST_CLERK' , 2200,null, 120, 50); insert into EMPLOYEES values(129,'Laura' ,'Bissot' ,'LBISSOT' ,'650.124.5234' ,'05/08/20','ST_CLERK' , 3300,null, 121, 50); insert into EMPLOYEES values(130,'Mozhe' ,'Atkinson' ,'MATKINSO','650.124.6234' ,'05/10/30','ST_CLERK' , 2800,null, 121, 50); insert into EMPLOYEES values(131,'James' ,'Marlow' ,'JAMRLOW' ,'650.124.7234' ,'05/02/16','ST_CLERK' , 2500,null, 121, 50); insert into EMPLOYEES values(132,'TJ' ,'Olson' ,'TJOLSON' ,'650.124.8234' ,'07/04/10','ST_CLERK' , 2100,null, 121, 50); insert into EMPLOYEES values(133,'Jason' ,'Mallin' ,'JMALLIN' ,'650.127.1934' ,'04/06/14','ST_CLERK' , 3300,null, 122, 50); insert into EMPLOYEES values(134,'Michael' ,'Rogers' ,'MROGERS' ,'650.127.1834' ,'06/08/26','ST_CLERK' , 2900,null, 122, 50); insert into EMPLOYEES values(135,'Ki' ,'Gee' ,'KGEE' ,'650.127.1734' ,'07/12/12','ST_CLERK' , 2400,null, 122, 50); insert into EMPLOYEES values(136,'Hazel' ,'Philtanker' ,'HPHILTAN','650.127.1634' ,'08/02/06','ST_CLERK' , 2200,null, 122, 50); insert into EMPLOYEES values(137,'Renske' ,'Ladwig' ,'RLADWIG' ,'650.121.1234' ,'03/07/14','ST_CLERK' , 3600,null, 123, 50); insert into EMPLOYEES values(138,'Stephen' ,'Stiles' ,'SSTILES' ,'650.121.2034' ,'05/10/26','ST_CLERK' , 3200,null, 123, 50); insert into EMPLOYEES values(139,'John' ,'Seo' ,'JSEO' ,'650.121.2019' ,'06/02/12','ST_CLERK' , 2700,null, 123, 50); insert into EMPLOYEES values(140,'Joshua' ,'Patel' ,'JPATEL' ,'650.121.1834' ,'06/04/06','ST_CLERK' , 2500,null, 123, 50); insert into EMPLOYEES values(141,'Trenna' ,'Rajs' ,'TRAJS' ,'650.121.8009' ,'03/10/17','ST_CLERK' , 3500,null, 124, 50); insert into EMPLOYEES values(142,'Curtis' ,'Davies' ,'CDAVIES' ,'650.121.2994' ,'05/01/29','ST_CLERK' , 3100,null, 124, 50); insert into EMPLOYEES values(143,'Randall' ,'Matos' ,'RMATOS' ,'650.121.2874' ,'06/03/15','ST_CLERK' , 2600,null, 124, 50); insert into EMPLOYEES values(144,'Peter' ,'Vargas' ,'PVARGAS' ,'650.121.2004' ,'06/07/09','ST_CLERK' , 2500,null, 124, 50); insert into EMPLOYEES values(145,'John' ,'Russell' ,'JRUSSEL' ,'011.44.1344.429268','04/10/01','SA_MAN' , 14000, .4, 100, 80); insert into EMPLOYEES values(146,'Karen' ,'Partners' ,'KPARTNER','011.44.1344.467268','05/01/05','SA_MAN' , 13500, .3, 100, 80); insert into EMPLOYEES values(147,'Alberto' ,'Errazuriz' ,'AERRAZUR','011.44.1344.429278','05/03/10','SA_MAN' , 12000, .3, 100, 80); insert into EMPLOYEES values(148,'Gerald' ,'Cambrault' ,'GCAMBRAU','011.44.1344.619268','07/10/15','SA_MAN' , 11000, .3, 100, 80); insert into EMPLOYEES values(149,'Eleni' ,'Zlotkey' ,'EZLOTKEY','011.44.1344.429018','08/01/29','SA_MAN' , 10500, .2, 100, 80); insert into EMPLOYEES values(150,'Peter' ,'Tucker' ,'PTUCKER' ,'011.44.1344.129268','05/01/30','SA_REP' , 10000, .3, 145, 80); insert into EMPLOYEES values(151,'David' ,'Bernstein' ,'DBERNSTE','011.44.1344.345268','05/03/24','SA_REP' , 9500, .25, 145, 80); insert into EMPLOYEES values(152,'Peter' ,'Hall' ,'PHALL' ,'011.44.1344.478968','05/08/20','SA_REP' , 9000, .25, 145, 80); insert into EMPLOYEES values(153,'Christopher','Olsen' ,'COLSEN' ,'011.44.1344.498718','06/03/30','SA_REP' , 8000, .2, 145, 80); insert into EMPLOYEES values(154,'Nanette' ,'Cambrault' ,'NCAMBRAU','011.44.1344.987668','06/12/09','SA_REP' , 7500, .2, 145, 80); insert into EMPLOYEES values(155,'Oliver' ,'Tuvault' ,'OTUVAULT','011.44.1344.486508','07/11/23','SA_REP' , 7000, .15, 145, 80); insert into EMPLOYEES values(156,'Janette' ,'King' ,'JKING' ,'011.44.1345.429268','04/01/30','SA_REP' , 10000, .35, 146, 80); insert into EMPLOYEES values(157,'Patrick' ,'Sully' ,'PSULLY' ,'011.44.1345.929268','04/03/04','SA_REP' , 9500, .35, 146, 80); insert into EMPLOYEES values(158,'Allan' ,'McEwen' ,'AMCEWEN' ,'011.44.1345.829268','04/08/01','SA_REP' , 9000, .35, 146, 80); insert into EMPLOYEES values(159,'Lindsey' ,'Smith' ,'LSMITH' ,'011.44.1345.729268','05/03/10','SA_REP' , 8000, .3, 146, 80); insert into EMPLOYEES values(160,'Louise' ,'Doran' ,'LDORAN' ,'011.44.1345.629268','05/12/15','SA_REP' , 7500, .3, 146, 80); insert into EMPLOYEES values(161,'Sarath' ,'Sewall' ,'SSEWALL' ,'011.44.1345.529268','06/11/03','SA_REP' , 7000, .25, 146, 80); insert into EMPLOYEES values(162,'Clara' ,'Vishney' ,'CVISHNEY','011.44.1346.129268','05/11/11','SA_REP' , 10500, .25, 147, 80); insert into EMPLOYEES values(163,'Danielle' ,'Greene' ,'DGREENE' ,'011.44.1346.229268','07/03/19','SA_REP' , 9500, .15, 147, 80); insert into EMPLOYEES values(164,'Mattea' ,'Marvins' ,'MMARVINS','011.44.1346.329268','08/01/24','SA_REP' , 7200, .1, 147, 80); insert into EMPLOYEES values(165,'David' ,'Lee' ,'DLEE' ,'011.44.1346.529268','08/02/23','SA_REP' , 6800, .1, 147, 80); insert into EMPLOYEES values(166,'Sundar' ,'Ande' ,'SANDE' ,'011.44.1346.629268','08/03/24','SA_REP' , 6400, .1, 147, 80); insert into EMPLOYEES values(167,'Amit' ,'Banda' ,'ABANDA' ,'011.44.1346.729268','08/04/21','SA_REP' , 6200, .1, 147, 80); insert into EMPLOYEES values(168,'Lisa' ,'Ozer' ,'LOZER' ,'011.44.1343.929268','05/03/11','SA_REP' , 11500, .25, 148, 80); insert into EMPLOYEES values(169,'Harrison' ,'Bloom' ,'HBLOOM' ,'011.44.1343.829268','06/03/23','SA_REP' , 10000, .2, 148, 80); insert into EMPLOYEES values(170,'Tayler' ,'Fox' ,'TFOX' ,'011.44.1343.729268','06/01/24','SA_REP' , 9600, .2, 148, 80); insert into EMPLOYEES values(171,'William' ,'Smith' ,'WSMITH' ,'011.44.1343.629268','07/02/23','SA_REP' , 7400, .15, 148, 80); insert into EMPLOYEES values(172,'Elizabeth' ,'Bates' ,'EBATES' ,'011.44.1343.529268','07/03/24','SA_REP' , 7300, .15, 148, 80); insert into EMPLOYEES values(173,'Sundita' ,'Kumar' ,'SKUMAR' ,'011.44.1343.329268','08/04/21','SA_REP' , 6100, .1, 148, 80); insert into EMPLOYEES values(174,'Ellen' ,'Abel' ,'EABEL' ,'011.44.1644.429267','04/05/11','SA_REP' , 11000, .3, 149, 80); insert into EMPLOYEES values(175,'Alyssa' ,'Hutton' ,'AHUTTON' ,'011.44.1644.429266','05/03/19','SA_REP' , 8800, .25, 149, 80); insert into EMPLOYEES values(176,'Jonathon' ,'Taylor' ,'JTAYLOR' ,'011.44.1644.429265','06/03/24','SA_REP' , 8600, .2, 149, 80); insert into EMPLOYEES values(177,'Jack' ,'Livingston' ,'JLIVINGS','011.44.1644.429264','06/04/23','SA_REP' , 8400, .2, 149, 80); insert into EMPLOYEES values(178,'Kimberely' ,'Grant' ,'KGRANT' ,'011.44.1644.429263','07/05/24','SA_REP' , 7000, .15, 149,null); insert into EMPLOYEES values(179,'Charles' ,'Johnson' ,'CJOHNSON','011.44.1644.429262','08/01/04','SA_REP' , 6200, .1, 149, 80); insert into EMPLOYEES values(180,'Winston' ,'Taylor' ,'WTAYLOR' ,'650.507.9876' ,'06/01/24','SH_CLERK' , 3200,null, 120, 50); insert into EMPLOYEES values(181,'Jean' ,'Fleaur' ,'JFLEAUR' ,'650.507.9877' ,'06/02/23','SH_CLERK' , 3100,null, 120, 50); insert into EMPLOYEES values(182,'Martha' ,'Sullivan' ,'MSULLIVA','650.507.9878' ,'07/06/21','SH_CLERK' , 2500,null, 120, 50); insert into EMPLOYEES values(183,'Girard' ,'Geoni' ,'GGEONI' ,'650.507.9879' ,'08/02/03','SH_CLERK' , 2800,null, 120, 50); insert into EMPLOYEES values(184,'Nandita' ,'Sarchand' ,'NSARCHAN','650.509.1876' ,'04/01/27','SH_CLERK' , 4200,null, 121, 50); insert into EMPLOYEES values(185,'Alexis' ,'Bull' ,'ABULL' ,'650.509.2876' ,'05/02/20','SH_CLERK' , 4100,null, 121, 50); insert into EMPLOYEES values(186,'Julia' ,'Dellinger' ,'JDELLING','650.509.3876' ,'06/06/24','SH_CLERK' , 3400,null, 121, 50); insert into EMPLOYEES values(187,'Anthony' ,'Cabrio' ,'ACABRIO' ,'650.509.4876' ,'07/02/07','SH_CLERK' , 3000,null, 121, 50); insert into EMPLOYEES values(188,'Kelly' ,'Chung' ,'KCHUNG' ,'650.505.1876' ,'05/06/14','SH_CLERK' , 3800,null, 122, 50); insert into EMPLOYEES values(189,'Jennifer' ,'Dilly' ,'JDILLY' ,'650.505.2876' ,'05/08/13','SH_CLERK' , 3600,null, 122, 50); insert into EMPLOYEES values(190,'Timothy' ,'Gates' ,'TGATES' ,'650.505.3876' ,'06/07/11','SH_CLERK' , 2900,null, 122, 50); insert into EMPLOYEES values(191,'Randall' ,'Perkins' ,'RPERKINS','650.505.4876' ,'07/12/19','SH_CLERK' , 2500,null, 122, 50); insert into EMPLOYEES values(192,'Sarah' ,'Bell' ,'SBELL' ,'650.501.1876' ,'04/02/04','SH_CLERK' , 4000,null, 123, 50); insert into EMPLOYEES values(193,'Britney' ,'Everett' ,'BEVERETT','650.501.2876' ,'05/03/03','SH_CLERK' , 3900,null, 123, 50); insert into EMPLOYEES values(194,'Samuel' ,'McCain' ,'SMCCAIN' ,'650.501.3876' ,'06/07/01','SH_CLERK' , 3200,null, 123, 50); insert into EMPLOYEES values(195,'Vance' ,'Jones' ,'VJONES' ,'650.501.4876' ,'07/03/17','SH_CLERK' , 2800,null, 123, 50); insert into EMPLOYEES values(196,'Alana' ,'Walsh' ,'AWALSH' ,'650.507.9811' ,'06/04/24','SH_CLERK' , 3100,null, 124, 50); insert into EMPLOYEES values(197,'Kevin' ,'Feeney' ,'KFEENEY' ,'650.507.9822' ,'06/05/23','SH_CLERK' , 3000,null, 124, 50); insert into EMPLOYEES values(198,'Donald' ,'OConnell' ,'DOCONNEL','650.507.9833' ,'07/06/21','SH_CLERK' , 2600,null, 124, 50); insert into EMPLOYEES values(199,'Douglas' ,'Grant' ,'DGRANT' ,'650.507.9844' ,'08/01/13','SH_CLERK' , 2600,null, 124, 50); insert into EMPLOYEES values(200,'Jennifer' ,'Whalen' ,'JWHALEN' ,'515.123.4444' ,'03/09/17','AD_ASST' , 4400,null, 101, 10); insert into EMPLOYEES values(201,'Michael' ,'Hartstein' ,'MHARTSTE','515.123.5555' ,'04/02/17','MK_MAN' , 13000,null, 100, 20); insert into EMPLOYEES values(202,'Pat' ,'Fay' ,'PFAY' ,'603.123.6666' ,'05/08/17','MK_REP' , 6000,null, 201, 20); insert into EMPLOYEES values(203,'Susan' ,'Mavris' ,'SMAVRIS' ,'515.123.7777' ,'02/06/07','HR_REP' , 6500,null, 101, 40); insert into EMPLOYEES values(204,'Hermann' ,'Baer' ,'HBAER' ,'515.123.8888' ,'02/06/07','PR_REP' , 10000,null, 101, 70); insert into EMPLOYEES values(205,'Shelley' ,'Higgins' ,'SHIGGINS','515.123.8080' ,'02/06/07','AC_MGR' , 12008,null, 101, 110); insert into EMPLOYEES values(206,'William' ,'Gietz' ,'WGIETZ' ,'515.123.8181' ,'02/06/07','AC_ACCOUNT' , 8300,null, 205, 110); -- 오류데이터: --insert into EMPLOYEES values(206,'William' ,'Gietz' ,'WGIETZ' ,'515.123.8181' ,'02/06/07','job_id' , 8300,null, 'manager_id', 'department_id'); commit; -------------------------- JOB_HISTORY 10 row----------------------- /* select Q'[insert into JOB_HISTORY values(]' ||Lpad(EMPLOYEE_ID ,5,' ') ||Q'[,]' ||Rpad(Q'[']'||START_DATE||'''',11,' ') ||Q'[,]' ||Rpad(Q'[']'||END_DATE||'''',11,' ') ||Q'[,]' ||Rpad(Q'[']'||JOB_ID||'''',13,' ') ||Q'[,]' ||Lpad(DEPARTMENT_ID,4,' ') ||Q'[);]' from JOB_HISTORY; */ insert into JOB_HISTORY values( 102,'01/01/13' ,'06/07/24' ,'IT_PROG' , 60); insert into JOB_HISTORY values( 101,'97/09/21' ,'01/10/27' ,'AC_ACCOUNT' , 110); insert into JOB_HISTORY values( 101,'01/10/28' ,'05/03/15' ,'AC_MGR' , 110); insert into JOB_HISTORY values( 201,'04/02/17' ,'07/12/19' ,'MK_REP' , 20); insert into JOB_HISTORY values( 114,'06/03/24' ,'07/12/31' ,'ST_CLERK' , 50); insert into JOB_HISTORY values( 122,'07/01/01' ,'07/12/31' ,'ST_CLERK' , 50); insert into JOB_HISTORY values( 200,'95/09/17' ,'01/06/17' ,'AD_ASST' , 90); insert into JOB_HISTORY values( 176,'06/03/24' ,'06/12/31' ,'SA_REP' , 80); insert into JOB_HISTORY values( 176,'07/01/01' ,'07/12/31' ,'SA_MAN' , 80); insert into JOB_HISTORY values( 200,'02/07/01' ,'06/12/31' ,'AC_ACCOUNT' , 90); commit; ----------------------------[ enable constraints ]---------------------------------- /* select Q'[alter table ]'||Rpad(TABLE_NAME,13,' ') ||Q'[ enable constraint ]'||Lpad(CONSTRAINT_NAME,27,' ') ||Q'[ exceptions into exceptions; ]' from user_constraints where CONSTRAINT_NAME like '%FK'; */ alter table LOCATIONS enable constraint LOCATIONS_COUNTRIES_FK exceptions into exceptions; alter table EMPLOYEES enable constraint EMPLOYEES_DEPARTMENTS_FK exceptions into exceptions; alter table JOB_HISTORY enable constraint JOB_HISTORY_DEPARTMENTS_FK exceptions into exceptions; alter table DEPARTMENTS enable constraint DEPARTMENTS_EMPLOYEES_FK exceptions into exceptions; alter table EMPLOYEES enable constraint EMPLOYEES_EMPLOYEES_FK exceptions into exceptions; alter table JOB_HISTORY enable constraint JOB_HISTORY_EMPLOYEES_FK exceptions into exceptions; alter table EMPLOYEES enable constraint EMPLOYEES_JOBS_FK exceptions into exceptions; alter table JOB_HISTORY enable constraint JOB_HISTORY_JOBS_FK exceptions into exceptions; alter table DEPARTMENTS enable constraint DEPARTMENTS_LOCATIONS_FK exceptions into exceptions; alter table COUNTRIES enable constraint COUNTRIES_REGIONS_FK exceptions into exceptions; ----------------------------[ check exceptions ]---------------------------------- select * from exceptions; --select * from dept where rowid in (select err_rowid from exceptions); --select * from emp where rowid in (select err_rowid from exceptions); /* */ select e.*,ex.constraint from EMPLOYEES e, exceptions ex where e.rowid = ex.err_rowid; /* EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CONSTRAINT ------ ------------- ---------- ------ ------------ ------ ---------- ------ ------------ 1111 MILLER CLERK 1112 23-JAN-82 1300 44 EMP_DEPT_FK 1111 MILLER CLERK 1112 23-JAN-82 1300 44 EMP_EMP_FK */